package com.zmj.sy.mom.srv.aps.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zmj.sy.mom.srv.aps.bean.entity.order.OrderBom;
import com.zmj.sy.mom.srv.aps.bean.entity.third.SinoCamPushPlan;
import com.zmj.sy.mom.srv.aps.bean.vo.nest.NestProPushListPlanResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.order.OrderPlanPushStatusResVo;
import com.zmj.sy.mom.srv.aps.bean.vo.workQueue.WorkQueueTaskDetailResVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.poi.ss.formula.functions.T;

import java.math.BigDecimal;
import java.util.List;

@Mapper
public interface SinoCamPushPlanMapper extends SyMapper<SinoCamPushPlan>{
    @Select("delete from tb_third_sinocam_pushplan where sino_cam_id = #{pid}")
    void deleteByPid(@Param("pid") Integer pid);

    @Select("SELECT SUM(p.plan_amount) FROM tb_third_sinocam_pushplan p WHERE p.order_id=#{orderId}")
    BigDecimal getPushCount(@Param("orderId") Integer orderId);

    @Select("DELETE FROM tb_third_sinocam_pushplan WHERE order_id=#{orderId}")
    void deletePreOrder(@Param("orderId") Integer orderId);

    @Select("DELETE FROM tb_third_sinocam_pushplan WHERE id=#{id}")
    void deleteWithId(@Param("id") Integer id);

    @Select("( SELECT " +
            "  p.processing_no AS packageCode, " +
            "  p.component_name AS assPartName, " +
            "  p.uni_mater_code AS uniCode, " +
            "  p.part_id AS partCode, " +
            "  p.part_name AS partName, " +
            "  p.plan_amount AS amount, " +
            "  p.create_date_time AS pushTime, " +
            "  p.`status` AS pushStatus, " +
            "  p.error_msg AS remark " +
            "  FROM " +
            "    tb_third_sinocam_pushplan p " +
            "    LEFT JOIN tb_order o ON p.order_id = o.id " +
            "  ${ew.customSqlSegment} " +
            "    AND p.`status` = 2 " +
            "  ) UNION ALL " +
            "  ( " +
            "  SELECT " +
            "    p.processing_no AS packageCode, " +
            "    p.component_name AS assPartName, " +
            "    p.uni_mater_code AS uniCode, " +
            "    p.part_id AS partCode, " +
            "    p.part_name AS partName, " +
            "    p.plan_amount AS amount, " +
            "    p.create_date_time AS pushTime, " +
            "    p.`status` AS pushStatus, " +
            "    p.error_msg AS remark " +
            "  FROM " +
            "    tb_third_sinocam_pushplan p " +
            "    LEFT JOIN tb_order o ON p.order_id = o.id " +
            "  ${ew.customSqlSegment} " +
            "    AND p.`status` <> 2 " +
            "ORDER BY " +
            "  p.`status` DESC)")
    Page<OrderPlanPushStatusResVo> planPushStatus(Page<T> page, @Param(Constants.WRAPPER) QueryWrapper<SinoCamPushPlan> query);

    @Select(" select SUM(plan.plan_amount) as planAmount, SUM(plan.plan_finish_amount) as planFinishAmount, awd.group_code as mesOrderCode " +
            " from tb_third_sinocam_pushplan plan " +
            " left join tb_aps_work_detail awd on awd.id = plan.nest_plan_id " +
            " WHERE (plan.need_date >= #{startDateTime} AND plan.need_date <= #{endDateTime} AND plan.version_brief = 'V2') " +
            " GROUP BY awd.group_code ORDER BY awd.group_code ASC ")
    List<SinoCamPushPlan> countFinishByDate(@Param("startDateTime") String startDateTime, @Param("endDateTime") String endDateTime);

    @Select(" select plan.id id,  o.serial_code orderCode, o.construct_no constructNo, ao.seq packageNo, pob.mater_name parentAssembly, awo.plm_id plmid, awo.mater_name partName, awo.texture sizeMaterial, awo.mater_high thick, awd.group_code groupNo, '版本号' version,  plan.plan_amount orderCnt, plan.plan_finish_amount finishCount, awd.plan_start_date_time planStartTime, awd.plan_end_date_time planEndTime " +
            " from tb_third_sinocam_pushplan plan " +
            " LEFT JOIN tb_aps_work_detail awd ON awd.id = plan.nest_plan_id " +
            " LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id " +
            " LEFT JOIN tb_order_bom pob ON awo.bom_pid = pob.id " +
            " LEFT JOIN tb_aps_order ao on awd.aps_order_id = ao.id " +
            " LEFT JOIN tb_order o on awd.order_id = o.id  " +
            " ${ew.customSqlSegment} ")
    Page<WorkQueueTaskDetailResVo> findByDate(Page<T> page, @Param(Constants.WRAPPER) QueryWrapper<SinoCamPushPlan> query);

    @Select(" SELECT " +
            "    p.id AS id, " +
            "    o.serial_code AS orderCode, " +
            "    o.mater_name AS productName, " +
            "    p.processing_no AS packageCode, " +
            "    p.component_name AS assPartName, " +
            "    p.component_id AS assPartCode, " +
            "    p.part_id AS partCode, " +
            "    p.part_name AS partName, " +
            "    p.uni_mater_code AS uniCode, " +
            "    p.replace_texture AS texture, " +
            "    p.replace_thickness AS thickness, " +
            "    p.plan_amount AS quantity, " +
            "    p.create_date_time AS pushTime, " +
            "    p.`status` AS `status`, " +
            "    p.push_type AS pushType, " +
            "    p.error_msg AS remark, " +
            "    awo.work_chain AS workChain, " +
            "    awd.group_code AS groupCode, " +
            "    p.mes_order_code AS mesOrderCode, " +
            "    p.need_date AS needDate, " +
            "    p.nest_plan_id AS nestPlanId, " +
            "    p.status AS status, " +
            "    p.error_msg AS remark " +
            "  FROM " +
            "    tb_third_sinocam_pushplan p " +
            "    LEFT JOIN tb_order o ON p.order_id = o.id " +
            "    LEFT JOIN tb_aps_work_detail awd ON p.nest_plan_id = awd.id LEFT JOIN tb_aps_work_order awo ON awd.aps_work_order_id = awo.id" +
            "  ${ew.customSqlSegment} ")
    Page<NestProPushListPlanResVo> pushStatusSelect2(Page<T> page, @Param(Constants.WRAPPER) QueryWrapper<SinoCamPushPlan> query);

    @Select(" select ob.order_id, ob.id, sum(spp.plan_finish_amount ) total_count " +
            " from tb_third_sinocam_pushplan spp " +
            " left join tb_aps_work_detail awd on spp.nest_plan_id = awd.id  " +
            " left join tb_aps_work_order awo on awo.id = awd.aps_work_order_id " +
            " left join tb_order_bom ob on awo.bom_id = ob.id " +
            " ${ew.customSqlSegment} ")
    List<OrderBom> findFinishCount(@Param(Constants.WRAPPER) QueryWrapper<SinoCamPushPlan> query);
}
